import configparser
import logging
import sys
from sqlalchemy import Integer, UniqueConstraint, text
from sqlalchemy import create_engine, Column, Table, MetaData, String, TIMESTAMP, Double, PrimaryKeyConstraint, inspect
import pandas as pd


def create_tmk2401Table():
    metadata = MetaData()
    # 定义表结构
    data_table = Table('tmk_2401', metadata,
                       Column('id', Integer, primary_key=True, autoincrement=True),
                       Column('STATION_ID', String(10)),
                       Column('INST_ID', String(10)),
                       Column('TS', TIMESTAMP(timezone=True)),
                       Column('DATE', String(16)),
                       Column('TIME', String(16)),
                       Column('FRAC_DAYS_SINCE_JAN1', Double),
                       Column('FRAC_HRS_SINCE_JAN1', Double),
                       Column('JULIAN_DAYS', Double),
                       Column('EPOCH_TIME', Double),
                       Column('ALARM_STATUS', Double),
                       Column('INST_STATUS', Double),
                       Column('CavityPressure', Double),
                       Column('CavityTemp', Double),
                       Column('DasTemp', Double),
                       Column('EtalonTemp', Double),
                       Column('WarmBoxTemp', Double),
                       Column('species', Double),
                       Column('MPVPosition', Double),
                       Column('OutletValve', Double),
                       Column('solenoid_valves', Double),
                       Column('CO', Double),
                       Column('CO2', Double),
                       Column('CO2_dry', Double),
                       Column('CH4', Double),
                       Column('CH4_dry', Double),
                       Column('H2O', Double),
                       Column('h2o_reported', Double),
                       Column('b_h2o_pct', Double),
                       Column('peak_14', Double),
                       Column('peak84_raw', Double),
                       UniqueConstraint("STATION_ID", "INST_ID", "TS", name="tmk2401_uix"))
    # 创建表
    metadata.create_all(engine)

def check_and_insert(df, station_id, inst_id, q_date):
    # Check if the DataFrame is empty
    if len(df) == 0:
        # Ask the user to enter yes/no
        answer = input("The DataFrame is empty. Do you want to insert a record? (yes/no): ").lower()
        
        # If the user answers "no", return
        if answer == "no":
            return
        
        # If the user answers "yes", insert a record to the DataFrame
        elif answer == "yes":
            # For demonstration purposes, let's assume the DataFrame has columns 'A' and 'B'
            # You can modify this part based on your DataFrame structure
            new_record = {'STATION_ID': station_id,
                        'INST_ID': inst_id,
                        'TS': pd.to_datetime(q_date + ' 00:00:00')}
            df = df.append(new_record, ignore_index=True)
            print(df)
            new_record = {'STATION_ID': station_id,
                        'INST_ID': inst_id,
                        'TS': pd.to_datetime(q_date + ' 23:59:59')}
            df = df.append(new_record, ignore_index=True)

            print("Two record has been inserted into the DataFrame.")
            
        else:
            print("Invalid input. Please enter 'yes' or 'no'.")
    elif len(df) == 1:
            new_record = {'STATION_ID': station_id,
                        'INST_ID': inst_id,
                        'TS': pd.to_datetime(q_date + '23:59:59')}
            df = df.append(new_record, ignore_index=True)
            print("Add end of day record for you automatically")
    else:
        print("The DataFrame is not empty.")
    return df


def convert_rdt_into_tmk(station_id, inst_id, q_date):
    logger.info('trying to convert rdt data into tmk data')
    # Query all data points ordered by timestamp
    # I use Mysql DATE() function here!!
    query = f"""SELECT * FROM rdt_2401 WHERE `DATE(TS)`='{q_date}' AND `STATION_ID`='{station_id}' AND `INST_ID`='{inst_id}' ORDER BY `TS`; """
    print(query)
    df_rdt_2401 = pd.read_sql_query(text(query), con=engine.connect())
    # Query data for specific station_id and inst_id
    df_rdt_2401 = check_and_insert(df_rdt_2401, station_id, inst_id, q_date)

    tmk_2401 = []
    for i in range(len(df_rdt_2401) - 1):
        tmk_2401.append(df_rdt_2401.iloc[i])
        current_ts = df_rdt_2401.iloc[i]['TS']
        next_ts = df_rdt_2401.iloc[i + 1]['TS']

        delta = (next_ts - current_ts).total_seconds()
        if delta > 6:
            logger.info(f'Detected data with an interval greater than 6 seconds,current timestamp:{current_ts}, next timestamp:{next_ts}')
            num_insertions = int((delta - 1) // 3)  # 计算需要插入的次数
            for j in range(1, num_insertions + 1):
                new_timestamp = current_ts + pd.Timedelta(seconds=3 * j)
                new_Asia_timestamp = new_timestamp.tz_convert('Asia/Shanghai')
                time_str = new_Asia_timestamp.strftime('%m/%d/%y %H:%M:%S') + '.' + str(new_Asia_timestamp.microsecond)[:3]
                DATE = time_str.split(' ')[0]
                TIME = time_str.split(' ')[1]
                tmk_2401.append(pd.Series(
                    {
                        'STATION_ID': station_id,
                        'INST_ID': inst_id,
                        'TS': new_timestamp,
                        'DATE': DATE,
                        'TIME': TIME,
                        'FRAC_DAYS_SINCE_JAN1': -999.99,
                        'FRAC_HRS_SINCE_JAN1': None,
                        'JULIAN_DAYS': None,
                        'EPOCH_TIME': None,
                        'ALARM_STATUS': None,
                        'INST_STATUS': None,
                        'CavityPressure': -999.99,
                        'CavityTemp': -99.99,
                        'DasTemp': -99.99,
                        'EtalonTemp': -99.99,
                        'WarmBoxTemp': None,
                        'species': None,
                        'MPVPosition': -9.99,
                        'OutletValve': -99999.99,
                        'solenoid_valves': -9.99,
                        'CO': -9.99,
                        'CO2': -999.99,
                        'CO2_dry': -999.9999,
                        'CH4': -9.99999,
                        'CH4_dry': -9.99999,
                        'H2O': -9.99,
                        'h2o_reported': None,
                        'b_h2o_pct': -9.99,
                        'peak_14': None,
                        'peak84_raw': -9.99
                    }
                )
                )
    # TODO: add statistic for inserted records
    tmk_2401.append(df_rdt_2401.iloc[-1])
    df_tmk_2401 = pd.DataFrame(tmk_2401).reset_index(drop=True)

    logger.info("Conversion successfully")
    return df_tmk_2401



def backup_tmk(df, output_path):
    logger.info("trying to backup the tmk data")
    # Convert to obtain the Timestamp object for the current date and set the time part to 0:00
    today = pd.Timestamp.now(tz='UTC').normalize()

    # Get the Timestamp object for yesterday's date and set the time part to 0:00
    yesterday = today - pd.Timedelta(days=1)
    start_of_yesterday = yesterday.normalize()
    df = df[(df['TS'] >= start_of_yesterday) & (df['TS'] < today)]

    df.to_csv(output_path, index=False)
    logger.info("Backup successfully!")


if __name__ == "__main__":
    config = configparser.ConfigParser()
    config.read('config.ini')
    # Extract log file path
    log_file_path = config['LOGGING']['log_file_path']
    # Configure logging
    logging.basicConfig(filename=log_file_path, level=logging.INFO)
    # Get logger instance
    logger = logging.getLogger(__name__)

    # Extract database connection parameters
    db_engine = config['DATABASE']['engine']
    db_host = config['DATABASE']['host']
    db_port = config['DATABASE']['port']
    db_name = config['DATABASE']['name']
    db_user = config['DATABASE']['user']
    db_password = config['DATABASE']['password']

    # Construct the database URL
    db_url = f'{db_engine}://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}'

    engine = create_engine(db_url)
    metadata = MetaData()
    inspector = inspect(engine)
    if (inspector.has_table('tmk_2401')):
        logger.info("Table tmk_2401 exists in the database.")
        rdt_table = Table('tmk_2401', metadata, autoload_with=engine)
    else:
        logger.info("creating table tmk_2401...")
        create_tmk2401Table()
        logger.info('created table tmk_2401 successfully!')

    df_tmk_2401 = convert_rdt_into_tmk('58448', 'S024', sys.argv[1])

    # upload tmk data to database
    logger.info('uploading the tmk data to database')
    df_tmk_2401.to_sql('tmk_2401', engine, index=False, if_exists='append')

    # backup tmk data
    # backup_tmk(df_tmk_2401, "backup.csv")

    logger.info('Done!')